﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using System.Data.SqlClient;
using Microsoft.Office.Interop.Excel;
using System.Data;
using System.Data.OleDb;


using System.Data.OleDb;
using System.Collections;
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;

namespace Catering.View_Operaction
{
    public class DataSetToExcelHelper
    {
        public DataSetToExcelHelper(string connectString)
        {
            this.ConnectString = connectString;
        }

        public void Export(ExcelTableFilter[] filters, string fileName)
        {
            System.Data.DataSet ds = this.Lookup(filters);
            this.Export(ds, fileName);
        }

        private System.Data.DataSet Lookup(ExcelTableFilter[] filters)
        {
            System.Data.DataSet ds = new System.Data.DataSet();

            foreach (ExcelTableFilter filter in filters)
            {
                this.Fill(ds, filter);
            }

            return ds;
        }

        private void Fill(System.Data.DataSet ds, ExcelTableFilter filter)
        {
            using (SqlDataAdapter sqlAdapter = new SqlDataAdapter(filter.SqlFilter, this.ConnectString))
            {
                sqlAdapter.Fill(ds, filter.TableName);
            }
        }

        public string ConnectString
        {
            get;
            set;
        }

        private void Export(System.Data.DataSet ds, string fileName)
        {
            bool success = DataSetToExcelHelper.DataSetToExcel(ds, fileName, false);

            if (!success)
                throw new Exception("数据导出失败！");
        }

        public static bool DataSetToExcel(System.Data.DataSet ds, string fileName, bool isShowExcle)
        {
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            try
            {
                if (app == null)
                {
                    return false;
                }

                app.Visible = isShowExcle;

                Workbooks workbooks = app.Workbooks;
                _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);

                _Worksheet sheet = workbook.Worksheets.get_Item(1) as _Worksheet;
                for (int i = 1; i < ds.Tables.Count; i++)
                {
                    workbook.Worksheets.Add(sheet, Type.Missing, Type.Missing, Type.Missing);
                }
                Sheets sheets = workbook.Worksheets;
                for (int k = 0; k < ds.Tables.Count; k++)
                {
                    _Worksheet worksheet = sheets.get_Item(k + 1) as _Worksheet;
                    worksheet.Name = ds.Tables[k].TableName;

                    for (int i = 0; i < ds.Tables[k].Columns.Count; i++)
                    {
                        Range ran = worksheet.Cells[1, i + 1] as Range;
                        ran.Value2 = ds.Tables[k].Columns[i].ColumnName;
                    }

                    for (int i = 0; i < ds.Tables[k].Rows.Count; i++)
                    {
                        for (int j = 0; j < ds.Tables[k].Columns.Count; j++)
                        {
                            Range ran = worksheet.Cells[i + 2, j + 1] as Range;
                            ran.Value2 = ds.Tables[k].Rows[i][j].ToString();
                        }
                    }
                }

                workbook.SaveCopyAs(fileName);
                workbook.Saved = true;
                app.UserControl = false;
                app.Quit();
                return true;
            }
            catch (Exception ee)
            {
                app.UserControl = false;
                app.Quit();
                return false;
            }
        }

        public static DataSet ExcelToDataSet(string path)
        {
            string connstr = null;
            OleDbConnection conn = null;
            OleDbDataAdapter da = null;
            DataSet ds = new DataSet();
            string sql = null;
            try
            {
                connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + path + ";Extended Properties='Excel 8.0;IMEX＝1;HDR=yes'";
                conn = new OleDbConnection(connstr);
                sql = "SELECT * FROM  [Recipes$];SELECT * FROM  [RecipesInfo$]";
                conn.Open();
                da = new OleDbDataAdapter(sql, connstr);
                ds.Clear();
                da.Fill(ds);
                conn.Close();
            }
            catch
            {
                connstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0 Xml;IMEX＝1;HDR=yes'";
                sql = "SELECT * FROM  [Recipes$]";
                conn = new OleDbConnection(connstr);
                conn.Open();
                da = new OleDbDataAdapter(sql, connstr);
                da.Fill(ds, "Recipes");
                sql = "SELECT * FROM  [RecipesInfo$]";
                da = new OleDbDataAdapter(sql, connstr);
                da.Fill(ds, "RecipesInfo");
                conn.Close();
            }
            return ds;
        }
    }

    public class ExcelTableFilter
    {
        public string TableName;

        public string SqlFilter;
    }
}
